package com.iam.api;

import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileWriter;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class MySQLTableExporter {
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("MySQL驱动加载失败", e);
        }
    }

    private static final String DB_URL = "jdbc:mysql://192.168.11.4:3306/jeesite2";
    private static final String USER = "root";
    private static final String PASS = "1234";
    private static final String OUTPUT_DIR = "E://mysql_export_" +
            LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));

    public static void main(String[] args) {
        File outputDir = new File(OUTPUT_DIR);
        if (!outputDir.exists()) outputDir.mkdirs();

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            List<String> tables = getTables(conn);
            System.out.println("开始导出数据库表数据...");

            for (String table : tables) {
                exportTable(conn, table, OUTPUT_DIR);
                System.out.println("已导出表: " + table);
            }

            System.out.println("导出完成！文件保存在: " + OUTPUT_DIR);
        } catch (Exception e) {
            System.err.println("数据库连接失败: " + e.getMessage());
        }
    }

    private static List<String> getTables(Connection conn) throws SQLException {
        List<String> tables = new ArrayList<>();
        try (Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SHOW TABLES")) {
            while (rs.next()) {
                String tableName = rs.getString(1);
                long rowCount = getTableRowCount(conn, tableName);
                if(rowCount > 1000000) {
                    System.out.println("表 [" + tableName + "] 行数: " + rowCount + ", 丢弃表语句");
                } else {
//                    System.out.println("表 [" + tableName + "] 行数: " + rowCount);
                    tables.add(tableName);
                }
            }

        }
        return tables;
    }

    /**
     * 获取表行数
     * @param conn
     * @param table
     * @return
     * @throws SQLException
     */
    private static long getTableRowCount(Connection conn, String table) throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + table)) {
            return rs.next() ? rs.getLong(1) : 0;
        }
    }

    private static void exportTable(Connection conn, String table, String dir) throws Exception {
//        if("t_dj_code".equals(table)
//                || "t_dj_data".equals(table)
//                || "t_hj_service_values".equals(table)
//                || "t_hj_service_values_result".equals(table)
//                || "t_hj_service_values_result_old".equals(table)
//                || "t_hj_service_values_result".equals(table)
//                || "t_hj_service_values_result".equals(table))
        String filePath = dir + "/" + table + ".sql";
        try (FileWriter writer = new FileWriter(filePath);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM " + table)) {

            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();

            while (rs.next()) {
                writer.write(buildInsertSQL(table, rs, cols) + "\n");
            }
        }
    }

    private static String buildInsertSQL(String table, ResultSet rs, int cols) throws SQLException {
        StringBuilder sql = new StringBuilder("INSERT INTO ").append(table).append(" VALUES (");
        for (int i = 1; i <= cols; i++) {
            Object val = rs.getObject(i);
            if (val instanceof String) sql.append("'").append(val.toString().replace("'", "''")).append("'");
            else if (val != null) sql.append(val);
            else sql.append("NULL");
            if (i < cols) sql.append(",");
        }
        return sql.append(");").toString();
    }
}
